﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading;
using System.Windows;
using System.Windows.Controls;

/*
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

*/

namespace DependencyDirector
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
                
        #region Set Global Objects

        public string strDatabaseName;

        public string strSQLServerName;
        public string strUserName;
        public string strUserPassword;

        public bool blnIsIntegrated;

        public SqlConnection SQLServerConnection;
        
        public DataTable dtAllObjects;
        public DataTable dtAllObjectsWithDefinition;
        public DataTable dtObjectsConnected;
        public DataTable dtPosConnObjects;
        public DataTable dtObjectExtendedInfo;

        /* Background workers */

        private BackgroundWorker BKW_GetReportServerListing;
        private BackgroundWorker BKW_GetSQLServerAllDatabases;

        /* Create instance of Login Window Class */

        //LoginWindow myLoginWindow = new LoginWindow();

        #endregion

        #region On Run

        public MainWindow()
        {
            InitializeComponent();

            //PromptLoginWindow();
                    
        }

        private void PromptLoginWindow()
        {
            //LoginWindow myLoginWindow = new LoginWindow();
            //myLoginWindow.ShowDialog();
        }

        private void SetAll()
        {

            // Get all Schema Names
            ddlDatabaseSchemas.DataContext = GetAllSchemas(dtAllObjects);

            if (ddlDatabaseSchemas.SelectedValue == null)
            {
                ddlDatabaseSchemas.SelectedItem = "dbo";
            }


            PopulateAllObjectsList(null);

            /* Tree View */

            //IEnumerable<string> dtAllObjectTypes = GetAllObjectTypes(dtAllObjects);
            DataTable dtAllObjectTypes = GetAllObjectTypes(dtAllObjects);

            lsvAllObjectTypes.DataContext = dtAllObjectTypes;

            //trvAllObjects.DataContext = dtAllObjectTypes;

            //trvAllObjects.DisplayMemberPath = "ObjectTypeFull";

            /* Tree View */
        }

        #endregion

        #region Set Properties

        private int GetCountofObjects(DataTable p_dtObjects)
        {
            //Count of Objects
            int intCountofObjects =
            (from s in p_dtObjects.AsEnumerable()
             //where s.Field<string>("SchemaName") != "sys"
             select s).Count();

            return intCountofObjects;
        }

        #endregion



        #region Populate UI Lists / Drop Downs

        public void Populate_ddlDatabases()
        {

            //LoginWindow myLoginWindow = new LoginWindow();
            //LoginWindow myLoginWindow = p_LoginWindow;
            
            //chkConnectionIsIntegrated.IsChecked = blnIsIntegrated;
            //txtSQLServerUser.Text = strUserName;
            //psdSQLServerPassword.Password = strUserPassword;

            strDatabaseName = "msdb";

            // Connect to the system database
            if (blnIsIntegrated)
            {
                SqlConnection SQLServerConnection = new SqlConnection(BuildConnectionString(strSQLServerName, null, null, strDatabaseName, true));

                DataTable dtAllDatabases;

                // Call background worker.
                dtAllDatabases = GetAllDatabases(SQLServerConnection);

                SQLServerConnection.Close();

                ddlDatabases.DataContext = dtAllDatabases;
                ddlDatabases.DisplayMemberPath = "Database_Name";

                strDatabaseName = null;

                //MessageBox.Show("Am I True?" + chkConnectionIsIntegrated.IsChecked.ToString());

            }
            else
            {
                SqlConnection SQLServerConnection = new SqlConnection(BuildConnectionString(strSQLServerName, strUserName, strUserPassword, strDatabaseName, false));
                DataTable dtAllDatabases = GetAllDatabases(SQLServerConnection);

                SQLServerConnection.Close();

                ddlDatabases.DataContext = dtAllDatabases;
                ddlDatabases.DisplayMemberPath = "Database_Name";

                strDatabaseName = null;
            }
        }

        private void PopulatelsbReportsConnectedList(string p_strObjectName)
        {

            /* Create the regular expression to find all things "Visual".
            System.Text.RegularExpressions.Regex searchTerm =
                new System.Text.RegularExpressions.Regex(@"(?<=\b(?:FROM|UPDATE|INTO|TABLE|JOIN)\s+)[\w.#\x5b\x5d]+");

            var queryMatchingFiles =
                from file in fileList
                where file.Extension == ".htm"
                let fileText = System.IO.File.ReadAllText(file.FullName)
                let matches = searchTerm.Matches(fileText)
                where searchTerm.Matches(fileText).Count > 0
                select new
                {
                    name = file.FullName,
                    matches = from System.Text.RegularExpressions.Match match in matches
                              select match.Value
                };

            var q = from s in dtAllObjects.AsEnumerable()
                    where searchTerm.Matches(fileText).Count > 0
                    select s;

            // Populate List of Objects
            dtObjectsSchemaFiltered = q.CopyToDataTable();
            lbAllTables.DataContext = dtObjectsSchemaFiltered;
            */
            /*
            var queryMatchingFiles =
                from file in fileList
                where file.Extension == ".htm"
                let fileText = System.IO.File.ReadAllText(file.FullName)
                let matches = searchTerm.Matches(fileText)
                where searchTerm.Matches(fileText).Count > 0
                select new
                {
                    name = file.FullName,
                    matches = from System.Text.RegularExpressions.Match match in matches
                              select match.Value
                };
             */

            /*
            string p_strObjectName = "usp_Cities_Search";

            SqlConnection SQLServerConnection = new SqlConnection(BuildConnectionString("MCSQLRPRT", null, null, "ReportServer", true));

            backgroundWorker1.RunWorkerAsync();
            
            lsbReportsConnected.DataContext = GetReportsConnected(SQLServerConnection, p_strObjectName);
            lsbReportsConnected.DisplayMemberPath = "ReportName";
            
            
            foreach (DataRow myDataRow in myDataTable.Rows)
            {
                //—————————————————-
                //—temp var
                //—————————————————-
                string myStringType = myDataRow["Type"].ToString();
                string myStringObject = myDataRow["Object"].ToString();
                string myStringCode = myDataRow["Code"].ToString();

                //—————————————————-
                //—If matches
                //—————————————————-
                if (Regex.IsMatch(myStringCode, this.mSearch, RegexOptions.IgnoreCase) == true)
                {
                    //—————————————————-
                    //—Format the code of scripts
                    //—————————————————-
                    myStringCode = this.FormatCode(myStringCode);

                    //—————————————————-
                    //—send to result DataTable
                    //—————————————————-
                    object[] myArrayObject = { myStringType, myStringObject, myStringCode };
                    myDataTableSearchResult.Rows.Add(myArrayObject);
                }
            }
            */
        
        }

        private void PopulateAllObjectsList(string p_strObjectType)
        {
            int intCountofObjects;
            DataTable dtObjectsSchemaFiltered = new DataTable();

            // Get all objects for the Schema selected
            if (p_strObjectType != null)
            {
                var q = from s in dtAllObjects.AsEnumerable()
                        where s.Field<string>("SchemaName") == ddlDatabaseSchemas.SelectedItem.ToString()
                        where s.Field<string>("ObjectType") == p_strObjectType
                        select s;

                // Populate List of Objects
                dtObjectsSchemaFiltered = q.CopyToDataTable();
                //lbAllTables.DataContext = dtObjectsSchemaFiltered;
                lsvAllObjects.DataContext = dtObjectsSchemaFiltered;


                /* TEMP */

                /*
                if (p_strObjectType == "P ")
               {

                    lsbObjectsConnected.DataContext = dtObjectsSchemaFiltered;
                    lsbObjectsConnected.DisplayMemberPath = "SchemaObjectName";
                }
                */

            }
            else
            {
                var q = from s in dtAllObjects.AsEnumerable()
                        where s.Field<string>("SchemaName") == ddlDatabaseSchemas.SelectedItem.ToString()
                        select s;

                intCountofObjects = GetCountofObjects(dtObjectsSchemaFiltered);

                if (intCountofObjects > 0)
                {
                    // Populate List of Objects
                    dtObjectsSchemaFiltered = q.CopyToDataTable();
                    //lbAllTables.DataContext = dtObjectsSchemaFiltered;
                    lsvAllObjects.DataContext = dtObjectsSchemaFiltered;
                }
            }


            // Get count of objects
            intCountofObjects = GetCountofObjects(dtObjectsSchemaFiltered);
            SetCountofObjectsLabel(intCountofObjects);

            tabItemObjects.Header = "Objects (" + intCountofObjects + ")";

            //lbAllTables.DisplayMemberPath = "ObjectName";
   
           //lbAllTables.DisplayMemberPath = "ObjecteName";
        }
        
        #endregion
        

        #region Populate UI Items
        
        private void SetCountofObjectsLabel(int p_intCountofObjects)
        {

            lblCountofTables.Content = p_intCountofObjects.ToString();
        }

        #endregion

        
        #region DataTables & Etc.

        private DataTable GetReportsConnected(SqlConnection p_SQLServerConnection, string p_strCommandText)
        {

            String sqlGetReportsConnected = "";

            sqlGetReportsConnected = sqlGetReportsConnected + ";WITH XMLNAMESPACES ( ";
            sqlGetReportsConnected = sqlGetReportsConnected + "DEFAULT ";
            sqlGetReportsConnected = sqlGetReportsConnected + "'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition', ";
            sqlGetReportsConnected = sqlGetReportsConnected + "'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd ";

            sqlGetReportsConnected = sqlGetReportsConnected + "), ";

            sqlGetReportsConnected = sqlGetReportsConnected + "CatalogCTE ";
            sqlGetReportsConnected = sqlGetReportsConnected + "AS ";
            sqlGetReportsConnected = sqlGetReportsConnected + "( ";
            sqlGetReportsConnected = sqlGetReportsConnected + "	SELECT		[name], ";
            sqlGetReportsConnected = sqlGetReportsConnected + "				[reportXML] = CAST(CAST([content] AS VARBINARY(MAX)) AS XML) ";
            //sqlGetReportsConnected = sqlGetReportsConnected + "	FROM		[MCSQLRPRT].ReportServer.dbo.CATALOG WITH (NOLOCK) ";
            sqlGetReportsConnected = sqlGetReportsConnected + "	FROM		ReportServer.dbo.CATALOG WITH (NOLOCK) ";
            sqlGetReportsConnected = sqlGetReportsConnected + ") ";


            sqlGetReportsConnected = sqlGetReportsConnected + "SELECT		[ReportName] = [name], ";
            sqlGetReportsConnected = sqlGetReportsConnected + "			[CommandType] = x.value('CommandType[1]', 'VARCHAR(50)'), ";
            sqlGetReportsConnected = sqlGetReportsConnected + "			[CommandText] = x.value('CommandText[1]','VARCHAR(50)') ";

            sqlGetReportsConnected = sqlGetReportsConnected + "FROM		[CatalogCTE] ct ";

            sqlGetReportsConnected = sqlGetReportsConnected + "CROSS APPLY	[reportXML].nodes('/Report/DataSets/DataSet/Query') r(x) ";

            sqlGetReportsConnected = sqlGetReportsConnected + "WHERE		x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure' ";
            sqlGetReportsConnected = sqlGetReportsConnected + "AND			NOT patindex('%.%', [name]) > 0 ";

            //sqlGetReportsConnected = sqlGetReportsConnected + "AND          x.value('CommandText[1]','VARCHAR(50)') = '" + p_strCommandText + "' ";

            sqlGetReportsConnected = sqlGetReportsConnected + "ORDER BY	[ReportName], [CommandText]; ";

            SqlDataAdapter adapter = new SqlDataAdapter(sqlGetReportsConnected, p_SQLServerConnection);

            DataTable dtReportsConnected = new DataTable("dtReportsConnected");

            adapter.Fill(dtReportsConnected);

            return dtReportsConnected;


        }

        private DataTable GetAllObjectsDataTable(SqlConnection p_SQLServerConnection)
        {
            String sqlGetAllObjects = "";

            sqlGetAllObjects = sqlGetAllObjects + "SELECT     \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "             SchemaName = s.name, \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "             ObjectName = o.name, \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "             ObjectCreated = o.create_date, \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "             ObjectModified = o.modify_date, \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "             SchemaObjectName = s.name + '.' + o.name, \r\n";
//            sqlGetAllObjects = sqlGetAllObjects + "           ObjectID = o.id, \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "             ObjectID = o.object_id, \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "             ObjectType = [type], \r\n";


            

            sqlGetAllObjects = sqlGetAllObjects + "             ObjectTypeFull = CASE WHEN [type] = 'P' THEN 'Stored Procedure' \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "                                 WHEN [type] = 'V' THEN 'View' \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "                                 WHEN [type] = 'U' THEN 'Table' \r\n";

            //sqlGetAllObjects = sqlGetAllObjects + "                                 WHEN [type] = 'IF' THEN 'Function' \r\n";
            //sqlGetAllObjects = sqlGetAllObjects + "                                 WHEN [type] = 'TF' THEN 'Function' \r\n";
            //sqlGetAllObjects = sqlGetAllObjects + "                                 WHEN [type] = 'FN' THEN 'Function' \r\n";

            sqlGetAllObjects = sqlGetAllObjects + "                                 WHEN [type] = 'IF' THEN 'Inline Table Function' \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "                                 WHEN [type] = 'TF' THEN 'Multi Table Function' \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "                                 WHEN [type] = 'FN' THEN 'Scalar Function' \r\n";
            //sqlGetAllObjects = sqlGetAllObjects + "                                 WHEN [type] = 'D' THEN 'Constraint' \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "                            ELSE 'Other' END,  \r\n";

            sqlGetAllObjects = sqlGetAllObjects + "             ObjectImagePath = CASE  WHEN [type] = 'P' THEN 'Images/icon_sp.gif' \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "                                     WHEN [type] = 'V' THEN 'Images/icon_view.gif' \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "                                     WHEN [type] = 'U' THEN 'Images/icon_table.gif' \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "                                     WHEN [type] = 'S' THEN 'System Object' \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "                                     WHEN [type] = 'IF' THEN 'Images/icon_function.gif' \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "                                     WHEN [type] = 'TF' THEN 'Images/icon_function.gif' \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "                                     WHEN [type] = 'FN' THEN 'Images/icon_function.gif' \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "                                     ELSE 'Other' END  \r\n";            
            
            sqlGetAllObjects = sqlGetAllObjects + "FROM         sys.objects o \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "LEFT JOIN    sys.schemas s WITH (NOLOCK)  \r\n";
            //sqlGetAllObjects = sqlGetAllObjects + "ON           s.schema_id = o.uid \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "ON           s.schema_id = o.schema_id \r\n";
            //sqlGetAllObjects = sqlGetAllObjects + "WHERE      o.xtype IN ('FN', 'IF', 'P', 'S', 'TR', 'TF', 'U', 'V', 'D', 'SN', 'PC', 'FS', 'AF', 'FT', 'TA')   \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "WHERE        o.type IN ('FN', 'IF', 'P', 'TR', 'TF', 'U', 'V', 'SN', 'PC', 'FS', 'AF', 'FT', 'TA')   \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "AND          o.name NOT LIKE 'dt_%'  \r\n";
            sqlGetAllObjects = sqlGetAllObjects + "ORDER BY	    s.name, o.type, o.name  \r\n";
            sqlGetAllObjects = sqlGetAllObjects + ";";

            SqlDataAdapter adapter = new SqlDataAdapter(sqlGetAllObjects, p_SQLServerConnection);
            DataTable dtAllObjects = new DataTable("dtAllObjects");
            adapter.Fill(dtAllObjects);
            
            p_SQLServerConnection.Close();

            //MessageBox.Show("foo");

            return dtAllObjects;

        }

        private DataTable GetAllObjectsWithDefinition(SqlConnection p_SQLServerConnection)
        {
            String sqlGetAllObjects = "";

            sqlGetAllObjects = sqlGetAllObjects + "SELECT object_id AS id, definition AS text, 1 AS colid, 1 AS number, CONVERT(bit, 0) AS Encrypted,\n";
            sqlGetAllObjects = sqlGetAllObjects + "CONVERT(bit, OBJECTPROPERTY(object_id, N'IsDefaultCnst')) AS IsDefault,                           \n";
            sqlGetAllObjects = sqlGetAllObjects + "CONVERT(bit, ISNULL(OBJECTPROPERTY(object_id, N'ExecIsQuotedIdentOn'),0)) AS QuotedIdentifier,    \n";
            sqlGetAllObjects = sqlGetAllObjects + "NULL AS ctext,                                                                                    \n";
            sqlGetAllObjects = sqlGetAllObjects + "o.name                                                                                            \n";
            sqlGetAllObjects = sqlGetAllObjects + "                                                                                                  \n";
            sqlGetAllObjects = sqlGetAllObjects + "FROM sys.sql_modules m  WITH (NOLOCK)                                                             \n";
            sqlGetAllObjects = sqlGetAllObjects + "JOIN sys.sysobjects o ON m.[object_id] = o.[id]                                                   \n";
            
            sqlGetAllObjects = sqlGetAllObjects + "WHERE                                                                                             \n";
            sqlGetAllObjects = sqlGetAllObjects + "(OBJECTPROPERTY(object_id, N'IsProcedure')<>0                                                     \n";
            sqlGetAllObjects = sqlGetAllObjects + "OR                                                                                                \n";
            sqlGetAllObjects = sqlGetAllObjects + "OBJECTPROPERTY(object_id, N'IsView')<>0                                                           \n";
            sqlGetAllObjects = sqlGetAllObjects + "OR                                                                                                \n";
            sqlGetAllObjects = sqlGetAllObjects + "OBJECTPROPERTY(object_id, N'IsTrigger')<>0                                                        \n";
            sqlGetAllObjects = sqlGetAllObjects + "OR                                                                                                \n";
            sqlGetAllObjects = sqlGetAllObjects + "OBJECTPROPERTY(object_id, N'IsInlineFunction')<>0                                                 \n";
            sqlGetAllObjects = sqlGetAllObjects + "OR                                                                                                \n";
            sqlGetAllObjects = sqlGetAllObjects + "OBJECTPROPERTY(object_id, N'IsScalarFunction')<>0                                                 \n";
            sqlGetAllObjects = sqlGetAllObjects + "OR                                                                                                \n";
            sqlGetAllObjects = sqlGetAllObjects + "OBJECTPROPERTY(object_id, N'IsTableFunction')<>0                                                  \n";
            sqlGetAllObjects = sqlGetAllObjects + "OR                                                                                                \n";
            sqlGetAllObjects = sqlGetAllObjects + "OBJECTPROPERTY(object_id, N'IsDefaultCnst')<>0                                                    \n";
            sqlGetAllObjects = sqlGetAllObjects + "OR                                                                                                \n";
            sqlGetAllObjects = sqlGetAllObjects + "object_id IN (SELECT object_id FROM sys.triggers WHERE parent_class=0)                            \n";
            sqlGetAllObjects = sqlGetAllObjects + ")                                                                                                 \n";

            sqlGetAllObjects = sqlGetAllObjects + "UNION ALL                                                                                         \n";

            sqlGetAllObjects = sqlGetAllObjects + "SELECT                                                                                            \n";
            sqlGetAllObjects = sqlGetAllObjects + "object_id AS id, definition AS text, parent_column_id AS colid, 0 AS number,                      \n";
            sqlGetAllObjects = sqlGetAllObjects + "CAST(0 as bit) AS Encrypted,                                                                      \n";
            sqlGetAllObjects = sqlGetAllObjects + "CAST(1 as bit) AS IsDefault,                                                                      \n";
            sqlGetAllObjects = sqlGetAllObjects + "CONVERT(bit, ISNULL(OBJECTPROPERTY(object_id, N'ExecIsQuotedIdentOn'),0)) AS QuotedIdentifier,    \n";
            sqlGetAllObjects = sqlGetAllObjects + "NULL AS ctext, name = NULL                                                                        \n";
            sqlGetAllObjects = sqlGetAllObjects + "FROM sys.default_constraints  WITH (NOLOCK)                                                       \n";

            sqlGetAllObjects = sqlGetAllObjects + "UNION ALL                                                                                         \n";

            sqlGetAllObjects = sqlGetAllObjects + "SELECT object_id AS id, definition AS text,                                                       \n";
            sqlGetAllObjects = sqlGetAllObjects + "CAST(1 as int) AS colid,                                                                          \n";
            sqlGetAllObjects = sqlGetAllObjects + "procedure_number AS number,                                                                       \n";
            sqlGetAllObjects = sqlGetAllObjects + "CAST(0 as bit) AS Encrypted,                                                                      \n";
            sqlGetAllObjects = sqlGetAllObjects + "CAST(0 as bit) AS IsDefault,                                                                      \n";
            sqlGetAllObjects = sqlGetAllObjects + "CONVERT(bit, ISNULL(OBJECTPROPERTY(object_id, N'ExecIsQuotedIdentOn'),0)) AS QuotedIdentifier,    \n";
            sqlGetAllObjects = sqlGetAllObjects + "NULL AS ctext, name = NULL                                                                        \n";
            sqlGetAllObjects = sqlGetAllObjects + "FROM sys.numbered_procedures  WITH (NOLOCK)                                                       \n";

            sqlGetAllObjects = sqlGetAllObjects + "ORDER BY id, colid, number                                                                        \n";
            sqlGetAllObjects = sqlGetAllObjects + ";";                                                                                               
                                                                                                                                                    
            SqlDataAdapter adapter = new SqlDataAdapter(sqlGetAllObjects, p_SQLServerConnection);
            DataTable dtAllObjectsWithDefinition = new DataTable("dtAllObjectsWithDefinition");
            adapter.Fill(dtAllObjectsWithDefinition);

            p_SQLServerConnection.Close();

            return dtAllObjectsWithDefinition;

        }

        private DataTable GetObjectsConnected(SqlConnection p_SQLServerConnection, string p_intObjectID)
        {
            String sqlGetObjectsConnected = "";

            sqlGetObjectsConnected = sqlGetObjectsConnected + " DECLARE @ObjectID bigint;\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + " SET @ObjectID = " + p_intObjectID.ToString() + "\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "        \n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + " SELECT		DISTINCT\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			[ObjectName] = (o1.name),\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			[name_type] = '[' + RTRIM(o1.xtype) + '] ' + (o1.name),\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			[ObjectType] = SUBSTRING(v2.name, 5, 16),\n";

            sqlGetObjectsConnected = sqlGetObjectsConnected + "         [ObjectImagePath] = CASE  WHEN [xtype] = 'P' THEN 'Images/icon_sp.gif' \r\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "                                   WHEN [xtype] = 'V' THEN 'Images/icon_view.gif' \r\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "                                   WHEN [xtype] = 'U' THEN 'Images/icon_table.gif' \r\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "                                   WHEN [xtype] = 'IF' THEN 'Images/icon_function.gif' \r\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "                                   WHEN [xtype] = 'TF' THEN 'Images/icon_function.gif' \r\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "                                   WHEN [xtype] = 'FN' THEN 'Images/icon_function.gif' \r\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "                                   ELSE 'Other' END  \r\n";    
            
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			--[updated] = SUBSTRING(u4.name, 1, 7),\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			--[selected] = SUBSTRING(w5.name, 1, 8),\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			--[column] = ISNULL(COL_NAME(d3.depid, d3.depnumber), '')\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + " FROM		[dbo].sysobjects o1,\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			master.dbo.spt_values v2,\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			[dbo].sysdepends d3,\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			master.dbo.spt_values u4,\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			master.dbo.spt_values w5,\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			[dbo].sysusers s6\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + " WHERE		o1.id = d3.depid\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			AND o1.xtype = SUBSTRING(v2.name, 1, 2) COLLATE database_default\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			AND v2.type = 'O9T'\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			AND u4.type = 'B'\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			AND u4.number = d3.resultobj\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			AND w5.type = 'B'\n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			AND w5.number = d3.readobj | d3.selall \n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			AND d3.id = @ObjectID \n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			AND o1.uid = s6.uid \n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			AND deptype < 2 \n";
            sqlGetObjectsConnected = sqlGetObjectsConnected + "			AND o1.name NOT LIKE 'dt_%' \n";

            sqlGetObjectsConnected = sqlGetObjectsConnected + " ORDER BY	[ObjectType], [ObjectName];\n";

            SqlDataAdapter adapter = new SqlDataAdapter(sqlGetObjectsConnected, p_SQLServerConnection);
            DataTable dtObjectsConnected = new DataTable("dtObjectsConnected");
            adapter.Fill(dtObjectsConnected);

            p_SQLServerConnection.Close();

            return dtObjectsConnected;

        }

        private DataTable GetPossibleConnectedObjects(SqlConnection p_SQLServerConnection, string p_intObjectID)
        {
            String sqlGetPosConnObjects = "";

            sqlGetPosConnObjects = sqlGetPosConnObjects + "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED                                                                                                                          \n"; 
            sqlGetPosConnObjects = sqlGetPosConnObjects + "SET NOCOUNT ON																																							 \n"; 
            sqlGetPosConnObjects = sqlGetPosConnObjects + "																																											 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "DECLARE		@ObjectName varchar(500);																																	 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "DECLARE		@ObjectSchema varchar(500);																																	 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "																																											 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "SET			@ObjectName = OBJECT_NAME(" + p_intObjectID + ");																											 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "																																											 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "SELECT		DISTINCT																																					 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "         [name_type]     = '[Job] - ' + jb.[name],                                                                                                                        \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "			[ObjectType]	= 'Job',																																		 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "			[ObjectImagePath]	= 'Images/icon_job.gif',                                \n";
                        
            sqlGetPosConnObjects = sqlGetPosConnObjects + "			[ObjectID]		= NULL,																																			 \n"; 
            sqlGetPosConnObjects = sqlGetPosConnObjects + "			[ObjectName]	= jb.[name],																																	 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "			[ObjectSubName] = js.[step_name]--,																																 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "			--js.database_name																																				 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "FROM		msdb.dbo.sysjobsteps js																																			 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "JOIN		msdb.dbo.sysjobs jb																																				 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "ON			jb.[job_id] = js.[job_id]																																	 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "WHERE		js.[command] LIKE '%[^0-9,^A-Z,^_]' + (CASE WHEN @ObjectSchema IS NOT NULL THEN @ObjectSchema + '.%.' + @ObjectName ELSE @ObjectName END) + '[^0-9,^A-Z,^_]%' \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "																																											 \n"; 
            sqlGetPosConnObjects = sqlGetPosConnObjects + "UNION ALL																																								 \n"; 
            sqlGetPosConnObjects = sqlGetPosConnObjects + "																																											 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "SELECT		DISTINCT																																					 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "         [name_type]     = '[' + RTRIM(ob.type) + '] ' + (ob.name),                                                                                                          \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "			[ObjectType]	= ob.[type],																																	 \n";

            sqlGetPosConnObjects = sqlGetPosConnObjects + "         [ObjectImagePath] = CASE  WHEN [type] = 'P' THEN 'Images/icon_sp.gif' \r\n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "                                   WHEN [type] = 'V' THEN 'Images/icon_view.gif' \r\n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "                                   WHEN [type] = 'U' THEN 'Table' \r\n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "                                   WHEN [type] = 'IF' THEN 'Images/icon_function.gif' \r\n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "                                   WHEN [type] = 'TF' THEN 'Images/icon_function.gif' \r\n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "                                   WHEN [type] = 'FN' THEN 'Images/icon_function.gif' \r\n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "                                   ELSE 'Other' END,  \r\n";    
            
            sqlGetPosConnObjects = sqlGetPosConnObjects + "			[ObjectID]		= sc.[id],																																		 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "			[ObjectName]	= ob.[name],																																	 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "			[ObjectSubName] = NULL																																			 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "																																											 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "																																											 \n"; 
            sqlGetPosConnObjects = sqlGetPosConnObjects + "FROM		syscomments sc																																					 \n"; 
            sqlGetPosConnObjects = sqlGetPosConnObjects + "JOIN		sysobjects ob																																					 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "ON			ob.[id] = sc.[id]																																	 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "WHERE		sc.[text] LIKE '%[^0-9,^A-Z,^_]' + (CASE WHEN @ObjectSchema IS NOT NULL THEN @ObjectSchema + '.%.' + @ObjectName ELSE @ObjectName END) + '[^0-9,^A-Z,^_]%'	 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "AND      	sc.[id] != " + p_intObjectID + "                        																									 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "																																											 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "ORDER BY	[ObjectType], [ObjectName], [ObjectSubName]																														 \n";
            sqlGetPosConnObjects = sqlGetPosConnObjects + "; \n";

            SqlDataAdapter adapter = new SqlDataAdapter(sqlGetPosConnObjects, p_SQLServerConnection);
            DataTable dtPosConnObjects = new DataTable("dtPosConnObjects");
            adapter.Fill(dtPosConnObjects);

            p_SQLServerConnection.Close();

            return dtPosConnObjects;

        }

        private DataTable GetAllObjectTypes(DataTable p_dtAllObjects)
        {
            var q = from s in p_dtAllObjects.AsEnumerable()
                    group s by s.Field<string>("ObjectTypeFull")
                    into g
                    select g.First()
                    ;

            DataTable dtAllObjectTypes = q.CopyToDataTable();

            return dtAllObjectTypes;
        }

        private DataTable GetObjectExtendedInfo(SqlConnection p_SQLServerConnection, string p_intObjectID)
        {
            String sqlGetObjectExtendedInfo = "";

            sqlGetObjectExtendedInfo = sqlGetObjectExtendedInfo + "SELECT		o.[object_id],                                   \n";
            sqlGetObjectExtendedInfo = sqlGetObjectExtendedInfo + "			o.[name],												 \n";
            sqlGetObjectExtendedInfo = sqlGetObjectExtendedInfo + "			o.[schema_id],											 \n";
            sqlGetObjectExtendedInfo = sqlGetObjectExtendedInfo + "			o.[parent_object_id],									 \n";
            sqlGetObjectExtendedInfo = sqlGetObjectExtendedInfo + "			o.[type],												 \n";
            sqlGetObjectExtendedInfo = sqlGetObjectExtendedInfo + "			o.[type_desc],											 \n";
            sqlGetObjectExtendedInfo = sqlGetObjectExtendedInfo + "			o.[create_date],										 \n";
            sqlGetObjectExtendedInfo = sqlGetObjectExtendedInfo + "			o.[modify_date]											 \n";
            sqlGetObjectExtendedInfo = sqlGetObjectExtendedInfo + "FROM		sys.objects o WITH (NOLOCK)								 \n";
            sqlGetObjectExtendedInfo = sqlGetObjectExtendedInfo + "WHERE		o.[type] IN ('PK','F')								 \n";
            sqlGetObjectExtendedInfo = sqlGetObjectExtendedInfo + "AND			o.[is_ms_shipped] = 0								 \n";
            sqlGetObjectExtendedInfo = sqlGetObjectExtendedInfo + "AND			o.[parent_object_id] = " + p_intObjectID + " \n";
            sqlGetObjectExtendedInfo = sqlGetObjectExtendedInfo + "ORDER BY	o.[type] DESC, o.[name]; \n";

            SqlDataAdapter adapter = new SqlDataAdapter(sqlGetObjectExtendedInfo, p_SQLServerConnection);
            DataTable dtObjectExtendedInfo = new DataTable("dtObjectExtendedInfo");
            adapter.Fill(dtObjectExtendedInfo);

            p_SQLServerConnection.Close();

            return dtObjectExtendedInfo;

        }

        private DataTable GetAllSQLJobs(SqlConnection p_SQLServerConnection)
        {
            string sqlAllSQLJobs = "";
            sqlAllSQLJobs = "EXEC msdb..sp_help_job;";
            SqlDataAdapter adapterAllSQLJobs = new SqlDataAdapter(sqlAllSQLJobs, p_SQLServerConnection);
            DataTable dtAllSQLJobs = new DataTable("dtAllSQLJobs");

            try
            {
                adapterAllSQLJobs.Fill(dtAllSQLJobs);
            }
            catch { }
            finally { p_SQLServerConnection.Close(); } 

            return dtAllSQLJobs;

        }
        
        private int CheckSQLServerVersion(SqlConnection p_SQLServerConnection)
        {
            string sqlVersionCheck = "";
            sqlVersionCheck = "SELECT [Version] = @@microsoftversion / POWER(2, 24);";
            SqlDataAdapter adapterVersionCheck = new SqlDataAdapter(sqlVersionCheck, p_SQLServerConnection);
            DataTable dtVersionCheck = new DataTable("dtVersionCheck");

            int SQLVersion = 0;

            try
            {
                /* Run SQL Version Check */
                adapterVersionCheck.Fill(dtVersionCheck);

                SQLVersion = Convert.ToInt16(dtVersionCheck.Rows[0]["Version"]);

            }
            catch { }
            finally { p_SQLServerConnection.Close(); }

            return SQLVersion;

        }

        #endregion



        #region Other DataTable Stuff

        private void GetAllObjects(SqlConnection p_SQLServerConnection)
        {
            dtAllObjects = GetAllObjectsDataTable(p_SQLServerConnection);
        }

        #endregion



        #region LinQ Stuff

        private IEnumerable<string> GetAllSchemas(DataTable p_dtAllObjects)
        {
            var q = from s in p_dtAllObjects.AsEnumerable()
                    select s.Field<string>("SchemaName");

            var x = q.Distinct();

            return x;
        }

        #endregion


        #region Background Workers
        /*
        private void GetSQLServerAllDatabases()
        {
            BKW_GetSQLServerAllDatabases = new BackgroundWorker();
            BKW_GetSQLServerAllDatabases.WorkerReportsProgress = true;
            BKW_GetSQLServerAllDatabases.WorkerSupportsCancellation = true;

            DataTable dtTemp = new DataTable();

            // Start work for Backgroundworker thread.
            BKW_GetSQLServerAllDatabases.DoWork += delegate(object s, DoWorkEventArgs args)
            {
                BackgroundWorker worker = s as BackgroundWorker;

                for (int i = 0; i < 1; i++)
                {
                    if (worker.CancellationPending)
                    {
                        args.Cancel = true;
                        return;
                    }

                    string p_strObjectName = null;//"usp_Cities_Search";

                    dtTemp = GetReportsConnected(SQLServerConnection, p_strObjectName);

                    //worker.ReportProgress(i + 10);
                }
            };

            // Progress Check (Nto used.)
            BKW_GetSQLServerAllDatabases.ProgressChanged += delegate(object s, ProgressChangedEventArgs args)
            {
                pgbReportServerProgress.Value = args.ProgressPercentage;
            };

            //Do when Finished
            BKW_GetSQLServerAllDatabases.RunWorkerCompleted += delegate(object s, RunWorkerCompletedEventArgs args)
            {
                btnStartReportListing.IsEnabled = true;
                btnCancelReportListing.IsEnabled = false;
                pgbReportServerProgress.IsIndeterminate = false;
                lblReportServerProgress.Content = "Done.";
                //pgbReportServerProgress.Value = 0;

                lsbReportsConnected.DataContext = dtTemp;
                lsbReportsConnected.DisplayMemberPath = "ReportName";

            };

            BKW_GetSQLServerAllDatabases.RunWorkerAsync();

        }
        */



        private DataTable GetAllDatabases(SqlConnection p_SQLServerConnection)
        {

            /* Get Database Listing */
            string sqlGetAllDatabases = "";

            //sqlGetAllDatabases = sqlGetAllDatabases + "SELECT  dtb.name AS [Database_Name], 'Server[@Name=' + quotename(CAST(serverproperty(N'Servername') AS sysname), '''') + ']' + '/Database[@Name=' + quotename(dtb.name, '''') + ']' AS [Database_Urn], case when DATABASEPROPERTY(dtb.name, 'IsShutDown') is null then 0x200 else 0 end | case when 1 = dtb.is_in_standby then 0x40 else 0 end | case when 1 = dtb.is_cleanly_shutdown then 0x80 else 0 end | case dtb.state when 1 then 0x2 when 2 then 0x8  when 3 then 0x4  when 4 then 0x10  when 5 then 0x100  when 6 then 0x20 else 1 end AS [Database_Status], dmi.mirroring_role AS [Database_MirroringRole], coalesce(dmi.mirroring_state + 1, 0) AS [Database_MirroringStatus], dtb.compatibility_level AS [Database_CompatibilityLevel], dtb.recovery_model AS [RecoveryModel], dtb.user_access AS [UserAccess], dtb.is_read_only AS [ReadOnly], dtb.name AS [Database_DatabaseName2] FROM    master.sys.databases AS dtb LEFT OUTER JOIN sys.database_mirroring AS dmi ON      dmi.database_id = dtb.database_id WHERE   ( CAST(case when dtb.name in ('master', 'model', 'msdb', 'tempdb') then 1 else dtb.is_distributor end AS bit) = 0 and CAST(isnull(dtb.source_database_id, 0) AS bit) = 0 )ORDER BY [Database_Name] ASC;";
            sqlGetAllDatabases = "SELECT [Database_Name] = [name], [Owner] = (owner_sid / POWER(2, 24)) FROM master.sys.databases WHERE (owner_sid / POWER(2, 24)) != 0 ORDER BY [name];";
            //sqlGetAllDatabases = "SELECT [Database_Name] = [name] FROM master.dbo.sysdatabases;";
            SqlDataAdapter adapter = new SqlDataAdapter(sqlGetAllDatabases, p_SQLServerConnection);
            DataTable dtAllDatabases = new DataTable("dtAllDatabases");

            /* Assume failure. */
            Boolean bolSuccess = false;

            BKW_GetSQLServerAllDatabases = new BackgroundWorker();
            BKW_GetSQLServerAllDatabases.WorkerReportsProgress = true;
            BKW_GetSQLServerAllDatabases.WorkerSupportsCancellation = true;


            pgbSQLServerConnection.IsIndeterminate = true;
            //txtSQLServerName.IsEnabled = false;
            txtSQLServerName.IsReadOnly = true;
            txtSQLServerUser.IsEnabled = false;
            psdSQLServerPassword.IsEnabled = false;

            BKW_GetSQLServerAllDatabases.RunWorkerAsync();

            BKW_GetSQLServerAllDatabases.DoWork += delegate(object s, DoWorkEventArgs args)
            {
                BackgroundWorker worker = s as BackgroundWorker;

                for (int i = 0; i < 1; i++)
                {
                    if (worker.CancellationPending)
                    {
                        args.Cancel = true;
                        return;
                    }

                    try
                    {
                        /* Run SQL Version Check */
                        int SQLVersion = CheckSQLServerVersion(p_SQLServerConnection);

                        /* If SQL is great then 8 which = SQL 2000 */
                        if (SQLVersion > 8)
                        {

                            adapter.Fill(dtAllDatabases);

                            if (dtAllDatabases.Rows.Count > 0)
                            {
                                bolSuccess = true;
                            }
                            else
                            {
                                bolSuccess = false;
                            }

                        }
                        else
                        {
                            MessageBox.Show("Sorry. Only SQL 2005 and 2008 are currently supported.");
                            bolSuccess = false;
                        }
                    }
                    catch (SqlException ex)
                    {
                        String SQLExceptionText = null;

                        for (int ie = 0; ie < ex.Errors.Count; ie++)
                        {
                            SQLExceptionText = SQLExceptionText + ex.Errors[ie].Message.ToString() + "\n";

                        }

                        MessageBox.Show("Connection Failed. \n\rSQL Error: \n\r" + SQLExceptionText);
                        dtAllDatabases = null;
                        bolSuccess = false;
                    }
                    catch (Exception ex)
                    {
                        String ExceptionText = ex.Message.ToString();
                        
                        MessageBox.Show("Connection Failed. \n\r" + ExceptionText);
                        dtAllDatabases = null;
                        bolSuccess = false;
                    }
                    //finally { p_SQLServerConnection.Close(); }

                }
            };

            //Do when Finished
            BKW_GetSQLServerAllDatabases.RunWorkerCompleted += delegate(object s, RunWorkerCompletedEventArgs args)
            {


                if (bolSuccess)
                {

                    pgbSQLServerConnection.IsIndeterminate = false;
                    p_SQLServerConnection.Close();

                    // Hide Login Area
                    expanderSQLLogin.IsExpanded = false;

                    // Move up Object browser
                    //ddlDatabases.Margin = new Thickness(13, 120, 0, 0);
                    //btnUseDatabase.Margin = new Thickness(246, 120, 0, 0);
                    //tabObjectBrowser.Margin = new Thickness(12, 150, 0, 30);
                    
                    txtSQLServerName.IsReadOnly = false;
                    txtSQLServerUser.IsEnabled = true;
                    psdSQLServerPassword.IsEnabled = true;
                    btnUseDatabase.IsEnabled = false;

                    ddlDatabases.IsEnabled = true;

                    ddlDatabases.BorderBrush = System.Windows.Media.Brushes.Orange;
                    
                    lblSQLServerName.Content = strSQLServerName;
                    lblSQLServerName.Foreground = System.Windows.Media.Brushes.PaleGreen;


                }
                else 
                {
                    pgbSQLServerConnection.IsIndeterminate = false;
                    p_SQLServerConnection.Close();
                    txtSQLServerName.IsReadOnly = false;
                    txtSQLServerUser.IsEnabled = true;
                    psdSQLServerPassword.IsEnabled = true;
                
                }
            };


            return dtAllDatabases;

        }
        
        private void GetReportServerListing(SqlConnection p_SQLServerConnection)
        {
            /* 
             * 
             * 
             * Seperate out into more methods 
             * 
             *              
             */

            BKW_GetReportServerListing = new BackgroundWorker();
            BKW_GetReportServerListing.WorkerReportsProgress = true;
            BKW_GetReportServerListing.WorkerSupportsCancellation = true;

            DataTable dtTemp = new DataTable();

            // Start work for Backgroundworker thread.
            BKW_GetReportServerListing.DoWork += delegate(object s, DoWorkEventArgs args)
            {
                BackgroundWorker worker = s as BackgroundWorker;

                for (int i = 0; i < 1; i++)
                {
                    if (worker.CancellationPending)
                    {
                        args.Cancel = true;
                        return;
                    }
                    // Temp Connection 
                    //SqlConnection SQLServerConnection = new SqlConnection(BuildConnectionString("MCSQLRPRT", null, null, "ReportServer", true));

                    string p_strObjectName = null; //"usp_Cities_Search";


                    dtTemp = GetReportsConnected(p_SQLServerConnection, p_strObjectName);

                    //worker.ReportProgress(i + 10);
                }
            };

            // Progress Check (Nto used.)
            BKW_GetReportServerListing.ProgressChanged += delegate(object s, ProgressChangedEventArgs args)
            {
                pgbReportServerProgress.Value = args.ProgressPercentage;
            };

            //Do when Finished
            BKW_GetReportServerListing.RunWorkerCompleted += delegate(object s, RunWorkerCompletedEventArgs args)
            {
                btnStartReportListing.IsEnabled = true;
                btnCancelReportListing.IsEnabled = false;
                pgbReportServerProgress.IsIndeterminate = false;
                lblReportServerProgress.Content = "Done.";
                //pgbReportServerProgress.Value = 0;

                lsbReportsConnected.DataContext = dtTemp;
                lsbReportsConnected.DisplayMemberPath = "ReportName";

            };

            BKW_GetReportServerListing.RunWorkerAsync();
            
        }


        #endregion
                
        #region Event Handlers

        #region Buttons 

        private void btnRefresh_Click(object sender, RoutedEventArgs e)
        {
             SetAll();
        }

        private void btnUseDatabase_Click(object sender, RoutedEventArgs e)
        {
            //LoginWindow myLoginWindow = new LoginWindow();
            strDatabaseName = ddlDatabases.Text.ToString();

            if (strDatabaseName != null)
            {

                if (blnIsIntegrated)
                {
                    // Reset connection to new Database
                    SQLServerConnection = new SqlConnection(BuildConnectionString(strSQLServerName, null, null, strDatabaseName, true));
                }
                else
                {
                    // Reset connection to new Database
                    SQLServerConnection = new SqlConnection(BuildConnectionString(strSQLServerName, strUserName, strUserPassword, strDatabaseName, false));
                }


                GetAllObjects(SQLServerConnection);
                SetAll();

                ddlDatabases.BorderBrush = System.Windows.Media.Brushes.Chartreuse;

                lblSQLServerName.Content = strSQLServerName + " » " + strDatabaseName;

                ddlDatabaseSchemas.IsEnabled = true;

                /* If a SSRS Reporting Server Database is Selected. */
                if (strDatabaseName == "ReportServer")
                {
                    btnStartReportListing.IsEnabled = true;

                }

            }
            else
            {
                MessageBox.Show("Login Failed");
            }
        }

        private void btnStartReportListing_Click(object sender, RoutedEventArgs e)
        {

            btnStartReportListing.IsEnabled = false;
            btnCancelReportListing.IsEnabled = true;

            lblReportServerProgress.Content = "Quering SSRS Server ...";

            Thread.Sleep(100);

            pgbReportServerProgress.IsIndeterminate = true;

            GetReportServerListing(SQLServerConnection);

        }

        private void btnCancelReportListing_Click(object sender, RoutedEventArgs e)
        {
            BKW_GetReportServerListing.CancelAsync();
        }

        private void btnServerLogin_Click(object sender, RoutedEventArgs e)
        {
            strSQLServerName = txtSQLServerName.Text;
            strUserName = txtSQLServerUser.Text;
            strUserPassword = psdSQLServerPassword.Password;
            blnIsIntegrated = chkConnectionIsIntegrated.IsChecked.Value;

            ddlDatabases.DataContext = "";
            ddlDatabaseSchemas.DataContext = "";
            trvAllObjects.DataContext = "";
            //lbAllTables.DataContext = "";
            lsvAllObjects.DataContext = "";
            lsbObjectsConnected.DataContext = "";

            Populate_ddlDatabases();

            /*
                txtSQLServerName.IsEnabled = false;
                txtSQLServerUser.IsEnabled = false;
                psdSQLServerPassword.IsEnabled = false;
                btnServerLogin.IsEnabled = false;
            */
                //MessageBox.Show(ddlDatabases.Items.Count.ToString());

        }

        #endregion

        #region Checkboxs

        private void chkConnectionIsIntegrated_Checked(object sender, RoutedEventArgs e)
        {
            txtSQLServerUser.IsEnabled = false;
            psdSQLServerPassword.IsEnabled = false;

            txtSQLServerUser.Visibility = Visibility.Hidden;
            psdSQLServerPassword.Visibility = Visibility.Hidden;
        }

        private void chkConnectionIsIntegrated_Unchecked(object sender, RoutedEventArgs e)
        {
            txtSQLServerUser.IsEnabled = true;
            psdSQLServerPassword.IsEnabled = true;

            txtSQLServerUser.Visibility = Visibility.Visible;
            psdSQLServerPassword.Visibility = Visibility.Visible;
        }

        #endregion

        #region Textboxes & Password Boxes

        private void txtSQLServerUser_GotFocus(object sender, RoutedEventArgs e)
        {
            if (txtSQLServerUser.Text == "User")
            {
                txtSQLServerUser.Text = "";
                txtSQLServerUser.FontStyle = FontStyles.Normal;
                txtSQLServerUser.Foreground = System.Windows.Media.Brushes.Black;
            }
        }

        private void txtSQLServerName_GotFocus(object sender, RoutedEventArgs e)
        {
            if (txtSQLServerName.Text == "Server Name")
            {
                txtSQLServerName.Text = "";
                txtSQLServerName.FontStyle = FontStyles.Normal;
                txtSQLServerName.Foreground = System.Windows.Media.Brushes.Black;
            }
        }

        private void txtSQLServerName_TextChanged(object sender, TextChangedEventArgs e)
        {
            if (txtSQLServerName.Text != "" && txtSQLServerName.Text != "Server Name")
            {
                btnServerLogin.IsEnabled = true;
            }
        }

        private void psdSQLServerPassword_GotFocus(object sender, RoutedEventArgs e)
        {
            if (psdSQLServerPassword.Password == "Password")
            {
                psdSQLServerPassword.Password = "";
                psdSQLServerPassword.FontStyle = FontStyles.Normal;
                psdSQLServerPassword.Foreground = System.Windows.Media.Brushes.Black;
            }
        }

        #endregion

        #region Listbox, ListView, TreeView

        private void trvAllObjects_SelectedItemChanged(object sender, RoutedPropertyChangedEventArgs<object> e)
        {
            //TreeViewItem x = e;

            try
            {
                DataRowView y = (DataRowView)trvAllObjects.SelectedItem;

                string z = y.Row["ObjectType"].ToString();

                PopulateAllObjectsList(z);

                dtAllObjectsWithDefinition = GetAllObjectsWithDefinition(SQLServerConnection);

                if (z == "U ")
                {
                    //lblTableKeys.Visibility = Visibility.Visible;
                    //txtTablesKeysCount.Visibility = Visibility.Visible;
                    //lsbTableKeys.Visibility = Visibility.Visible;
                }
                else
                {
                    //lblTableKeys.Visibility = Visibility.Hidden;
                    //txtTablesKeysCount.Visibility = Visibility.Hidden;
                    //lsbTableKeys.Visibility = Visibility.Hidden;
                }
            
            }
            catch
            {
            }
            //MessageBox.Show(z);

            //MessageBox.Show(((TreeViewItem)e.NewValue).Header.ToString());

            /*
            IEnumerable<string> a = lbAllTables.DataContext.ToString();

            var q = from s in a.AsEnumerable()
                    where a.Field<string>("ObjectType") == trvAllObjects.SelectedValue.ToString()
                    select s;
            var x = q.Distinct();

            lbAllTables.DataContext = x;
             */

        }

        private void lbAllTables_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {

            try
            {
                DataRowView y = (DataRowView)lbAllTables.SelectedItem;

                string z = y.Row["ObjectID"].ToString();
                string strObjectType = y.Row["ObjectType"].ToString();

                // Populate Object Info
                txtSelectedObjectID.Text = z;
                txtSelectedObjectCreated.Text = y.Row["ObjectCreated"].ToString();
                txtSelectedObjectModified.Text = y.Row["ObjectModified"].ToString();

                /*
                DataTable dtObjectsSchemaFiltered = new DataTable();

                dtObjectsSchemaFiltered.Columns.Add("SchemaObjectName");
                dtObjectsSchemaFiltered.Rows.Add(z);

                lsbObjectsConnected.DataContext = dtObjectsSchemaFiltered;
                lsbObjectsConnected.DisplayMemberPath = "SchemaObjectName";

                
                System.Text.RegularExpressions.Regex searchTerm = new System.Text.RegularExpressions.Regex(@"(?<=\b(?:FROM|UPDATE|INTO|TABLE|JOIN)\s+)[\w.#\x5b\x5d]+");

                var q = from s in dtAllObjectsWithDefinition.AsEnumerable()
                        where searchTerm.Matches(z).Count > 0
                        select s;
                

                // Populate List of Objects
                dtAllObjectsWithDefinition = q.CopyToDataTable();
                */

                /*
                var q = from s in dtObjectsConnected.AsEnumerable()
                        where s.Field<string>("name").StartsWith("dt_")
                        select s;
                            
                dtObjectsConnected = q.CopyToDataTable();
                */

                /* Uses ?? ... */
                lsbObjectsConnected.IsEnabled = true;
                DataTable dtObjectsConnected = GetObjectsConnected(SQLServerConnection, z);

                lsvObjectUses.DataContext = dtObjectsConnected;

                //lsbObjectsConnected.DataContext = dtObjectsConnected;
                //lsbObjectsConnected.DisplayMemberPath = "name_type";




                if (strObjectType == "U ") 
                { 
                    tabObjectUses.SelectedIndex = 1;
                    tabItemUses.IsEnabled = false;

                    //Extended Info (Keys)
                    DataTable dtObjectExtendedInfo = GetObjectExtendedInfo(SQLServerConnection, z);
                    lsbTableKeys.DataContext = dtObjectExtendedInfo;
                    lsbTableKeys.DisplayMemberPath = "name";

                    //txtTablesKeysCount.Text = dtObjectExtendedInfo.Rows.Count.ToString();




                } else { 
                    tabObjectUses.SelectedIndex = 0;
                    tabItemUses.IsEnabled = true;
                }

                /* Used by ?? ... */
                lsbObjectsPossConnected.IsEnabled = true;
                DataTable dtPosConnObjects = GetPossibleConnectedObjects(SQLServerConnection, z);

                lsbObjectsPossConnected.DataContext = dtPosConnObjects;
                lsbObjectsPossConnected.DisplayMemberPath = "name_type";

                //Test
                lsvMaybeUsedBy.DataContext = dtPosConnObjects;


                /* Update Count on Tabs */

                tabItemMaybeUsedBy.Header = "Maybe Used By (" + dtPosConnObjects.Rows.Count.ToString() + ")";
                tabItemUses.Header = "Uses (" + dtObjectsConnected.Rows.Count.ToString() + ")";

            }
            catch { }

        }

        private void lsbObjectsConnected_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            try
            {
                DataRowView y = (DataRowView)lbAllTables.SelectedItem;

                string z = y.Row["ObjectName"].ToString();

                /*
                //create a dialog instance
                ProgressDialog dlg = new ProgressDialog();
                dlg.Owner = this;
                dlg.DialogText = "hello world";

                //we cannot access the user interface on the worker thread, but we
                //can submit an arbitrary object to the RunWorkerThread method
                int startValue = int.Parse("1");

                //start processing and submit the start value
                dlg.RunWorkerThread(startValue, PopulatelsbReportsConnectedList);
                */

                PopulatelsbReportsConnectedList(z);
            }
            catch { }
        }

        #endregion

        #region Comboboxes

        private void ddlDatabases_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            try
            {
                if (ddlDatabases.SelectedValue.ToString() != "msdb" && ddlDatabases.SelectedValue.ToString() != "master" && ddlDatabases.SelectedValue != null)
                {
                    btnUseDatabase.IsEnabled = true;
                }
            }
            catch { }
        }

        #endregion

        #region Other

        private void MenuItem_LoginPrompt_Click(object sender, RoutedEventArgs e)
        {
            PromptLoginWindow();
        }

        #endregion

        #endregion


        #region Helping Methods

        private static string BuildConnectionString(string p_DataSource, string p_UserName, string p_UserPassword, string p_DatabaseName, bool p_IsIntegrated)
        {
            if (p_IsIntegrated)
            {
                string ConnectionString = "Trusted_Connection=true;Application Name=Antimuon;";

                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);

                builder.DataSource = p_DataSource;
                builder.InitialCatalog = p_DatabaseName;

                return builder.ConnectionString;
            }
            else 
            {
                string ConnectionString = "Trusted_Connection=false;Application Name=Antimuon;";

                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);

                builder.DataSource = p_DataSource;
                builder.InitialCatalog = p_DatabaseName;
                builder.UserID = p_UserName;
                builder.Password = p_UserPassword;

                return builder.ConnectionString;
            }


        }

        #endregion

        private void lsvAllObjects_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            try
            {
                DataRowView y = (DataRowView)lsvAllObjects.SelectedItem;

                string z = y.Row["ObjectID"].ToString();
                string strObjectType = y.Row["ObjectType"].ToString();

                // Populate Object Info
                txtSelectedObjectID.Text = z;
                txtSelectedObjectCreated.Text = y.Row["ObjectCreated"].ToString();
                txtSelectedObjectModified.Text = y.Row["ObjectModified"].ToString();

                /* Uses ?? ... */
                lsbObjectsConnected.IsEnabled = true;
                DataTable dtObjectsConnected = GetObjectsConnected(SQLServerConnection, z);

                lsvObjectUses.DataContext = dtObjectsConnected;

                if (strObjectType == "U ")
                {
                    tabObjectUses.SelectedIndex = 1;
                    tabItemUses.IsEnabled = false;

                    //Extended Info (Keys)
                    DataTable dtObjectExtendedInfo = GetObjectExtendedInfo(SQLServerConnection, z);
                    lsbTableKeys.DataContext = dtObjectExtendedInfo;
                    lsbTableKeys.DisplayMemberPath = "name";

                    //txtTablesKeysCount.Text = dtObjectExtendedInfo.Rows.Count.ToString();

                }
                else
                {
                    tabObjectUses.SelectedIndex = 0;
                    tabItemUses.IsEnabled = true;
                }

                /* Used by ?? ... */
                lsbObjectsPossConnected.IsEnabled = true;
                DataTable dtPosConnObjects = GetPossibleConnectedObjects(SQLServerConnection, z);

                lsbObjectsPossConnected.DataContext = dtPosConnObjects;
                lsbObjectsPossConnected.DisplayMemberPath = "name_type";

                //Test
                lsvMaybeUsedBy.DataContext = dtPosConnObjects;

                /* Update Count on Tabs */
                tabItemMaybeUsedBy.Header = "Maybe Used By (" + dtPosConnObjects.Rows.Count.ToString() + ")";
                tabItemUses.Header = "Uses (" + dtObjectsConnected.Rows.Count.ToString() + ")";

            }
            catch { }
        }

        private void lsvAllObjectTypes_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            try
            {
                DataRowView y = (DataRowView)lsvAllObjectTypes.SelectedItem;

                string z = y.Row["ObjectType"].ToString();

                PopulateAllObjectsList(z);

                dtAllObjectsWithDefinition = GetAllObjectsWithDefinition(SQLServerConnection);

                tabObjectBrowser.SelectedIndex = 1;

                if (z == "U ")
                {
                    //lblTableKeys.Visibility = Visibility.Visible;
                    //txtTablesKeysCount.Visibility = Visibility.Visible;
                    //lsbTableKeys.Visibility = Visibility.Visible;
                }
                else
                {
                    //lblTableKeys.Visibility = Visibility.Hidden;
                    //txtTablesKeysCount.Visibility = Visibility.Hidden;
                    //lsbTableKeys.Visibility = Visibility.Hidden;
                }

            }
            catch
            {
            }
        }
        

        /*
         static string GetConnectionStringByName(string name)
         {
             // Assume failure.
             string returnValue = null;

             // Look for the name in the connectionStrings section.
             ConnectionStringSettings settings =
                 ConfigurationManager.ConnectionStrings[name];

             // If found, return the connection string.
             if (settings != null)
                 returnValue = settings.ConnectionString;

             return returnValue;
         }
         */

    }
}
